package com.southgis.ibase.sql.func;

import com.southgis.ibase.sql.utils.SqlUtil;
import com.southgis.ibase.sql.enums.ColumnTypeEnum;

import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

public class MysqlFunc extends CommonFunc
{
	@Override
	public String charType(int len)
	{
		if(len<0 || len>65535) {
			return getDialect().getTypeName(java.sql.Types.CLOB);
		}else if(len==0) {
			String typeName=getDialect().getTypeName(java.sql.Types.VARCHAR, 1, 0, 0);
			int pos=typeName.indexOf('(');
			if(pos>0) typeName=typeName.substring(0,pos).trim();
			return typeName;
		}else{
			return getDialect().getTypeName(java.sql.Types.VARCHAR, len, 0, 0);
		}
	}

	//标准写法
//	@Override
//	public String binaryType(int len)
//	{
//	}

	@Override
	public String datetimeType()
	{
		return "datetime";
	}
	
	@Override
	public String doubleType()
	{
		return "double";
	}
	
	//标准写法
//	@Override
//	public String decimalType(int len,int scale)
//	{
//	}
//	
//	@Override
//	public String intType(int width)
//	{
//	}

	//标准写法
//	@Override
//	public String notEmptyCharWhere(String fieldName)
//	{
//	}
	
	//标准写法
//	@Override
//	public String emptyCharWhere(String fieldName)
//	{
//	}

	//标准写法
//	@Override
//	public String concatChar(String... charVals)
//	{
//	}
	
	@Override
	public String getCharToDateInvoke(String charVal, int fmt)
	{
		String sql="";
		switch(fmt)
		{
		case 0: //yyyy-MM-dd HH:mm:ss
			sql="STR_TO_DATE("+charVal+",'%Y-%m-%d %H:%i:%s')";
			break;
		case 1: //yyyyMMddHHmmss
			sql="STR_TO_DATE("+charVal+",'%Y%m%d %H%i%s')";
			break;
		case 10: //yyyy-MM-dd
			sql="STR_TO_DATE("+charVal+",'%Y-%m-%d')";
			break;
		case 11: //yyyyMMdd
			sql="STR_TO_DATE("+charVal+",'%Y%m%d')";
			break;
		default://默认格式，即不用格式串
			sql=getCastInvoke(charVal,java.sql.Types.TIMESTAMP,0,0);
			break;
		}

		return sql;
	}
	
	//标准写法
//	@Override
//	public String getYearInvoke(String dateVal)
//	{
//	}

	//标准写法
//	@Override
//	public String getDateConst(String fmtVal)
//	{
//	}

	//mysql对varchar转换不支持，需转为char类型
	@Override
	public String getCastInvoke(String orgVal,int targetType,int length, int scale)
	{
		if(targetType==Types.VARCHAR)
			targetType=Types.CHAR;
		else if(targetType==Types.NVARCHAR)
			targetType=Types.NCHAR;

		return super.getCastInvoke(orgVal, targetType, length, scale);
	}
	
	@Override
	public String pageSql(String sql, int offset, int pageSize)
	{
		return sql + " limit " + offset + "," + pageSize;
	}
	
	//标准写法
//	@Override
//	public String countSql(String sql)
//	{
//	}

	@Override
	public String sqlOfTableCount(String tableName, String schema)
	{
		return "SELECT COUNT(*) AS " + IExclusiveDatabaseFunc.ResultColumns.COUNT
				+ " FROM INFORMATION_SCHEMA.TABLES "
				+ " WHERE "
				+ " lower(TABLE_SCHEMA) = '" + schema.toLowerCase() + "' AND "
				+ " lower(TABLE_NAME) = '" + tableName.toLowerCase() + "'";
	}
	
	@Override
	public String sqlOfListTable(String name, TableTypeEnum typeEnum, String schema)
	{
		StringBuilder sbSql=new StringBuilder();
		sbSql.append("SELECT TABLE_NAME AS ").append(IExclusiveDatabaseFunc.ResultColumns.TABLE_NAME)
			.append(",TABLE_COMMENT AS ").append(IExclusiveDatabaseFunc.ResultColumns.TABLE_COMMENT)
			.append(",CASE TABLE_TYPE WHEN 'VIEW' THEN 2 ELSE 1 END AS ").append(IExclusiveDatabaseFunc.ResultColumns.TABLE_TYPE)
			.append(" FROM INFORMATION_SCHEMA.TABLES ")
			.append(" WHERE lower(TABLE_SCHEMA) = '").append(schema.toLowerCase()).append("'");
		
		int type = typeEnum.getType();
		if(!StringUtils.isEmpty(name)) {
			sbSql.append(" AND ");
			
			sbSql.append(" lower(TABLE_NAME) ");
			if((type&0x100)==0x100)
				sbSql.append(" = ");
			else {
				sbSql.append(" like ");
				name=name.replace("_", "\\_");
			}
			sbSql.append("'").append(name.toLowerCase()).append("'");
		}
		if(type!=0) {
			sbSql.append(" AND ");
			sbSql.append(" (");
			if((type&1)==1) {
				sbSql.append("TABLE_TYPE='BASE TABLE'");
			}
			if((type&2)==2) {
				if((type&1)==1) sbSql.append(" OR ");
				sbSql.append("TABLE_TYPE='VIEW'");
			}
			sbSql.append(")");
		}
		return sbSql.toString();
	}

	@Override
	public String sqlOfListColumns(String tableName, String schema)
	{
		//设置查询类型：0不加条件，1 用等于，-1 用like
		int queryType=0;
		if(!StringUtils.isEmpty(tableName)) {
			queryType=tableName.indexOf('%')<0?1:-1;
			if(queryType==-1) {
				tableName=tableName.replace("_", "\\_");
			}
		}
		return "SELECT tc.TABLE_NAME as " + IExclusiveDatabaseFunc.ResultColumns.TABLE_NAME
				+ ",tc.COLUMN_NAME as " + IExclusiveDatabaseFunc.ResultColumns.COLUMN_NAME
				+ ",tc.COLUMN_COMMENT as " + IExclusiveDatabaseFunc.ResultColumns.COLUMN_COMMENT
				+ ",CASE tc.IS_NULLABLE WHEN 'NO' THEN 'N' ELSE 'Y' END as " + IExclusiveDatabaseFunc.ResultColumns.COLUMN_NULLABLE
				+ ",tc.DATA_TYPE as " + IExclusiveDatabaseFunc.ResultColumns.DATA_TYPE
				+ ",IFNULL(tc.NUMERIC_PRECISION,tc.CHARACTER_MAXIMUM_LENGTH) as " + IExclusiveDatabaseFunc.ResultColumns.DATA_LENGTH
				+ ",tc.NUMERIC_SCALE as " + IExclusiveDatabaseFunc.ResultColumns.DATA_SCALE
				+ ",NULL as " + IExclusiveDatabaseFunc.ResultColumns.DATA_DEFINE
				+ " FROM INFORMATION_SCHEMA.COLUMNS tc"
				+ " WHERE "
				+ " lower(tc.TABLE_SCHEMA) = '" + schema.toLowerCase() + "'"
				+ (queryType==0?"":(" AND lower(tc.TABLE_NAME) " +(queryType==1?"=":" like ")+" '" + tableName.toLowerCase() + "'"));
	}

	@Override
	public String sqlOfListConstraint(ConstraintTypeEnum typeEnum, String tableName,String schema)
	{
		StringBuilder sbSql=new StringBuilder();
		sbSql.append("SELECT uc.CONSTRAINT_NAME as ").append(IExclusiveDatabaseFunc.ResultColumns.CONS_NAME)
				.append(",CASE uc.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN 1 WHEN 'UNIQUE' THEN 2 WHEN 'FOREIGN KEY' THEN 4 ELSE 0 END as ").append(IExclusiveDatabaseFunc.ResultColumns.CONS_TYPE)
				.append(",cc.COLUMN_NAME as ").append(IExclusiveDatabaseFunc.ResultColumns.COLUMN_NAME)
				.append(" FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS uc left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE cc ")
				.append(" on uc.CONSTRAINT_SCHEMA=cc.CONSTRAINT_SCHEMA and uc.TABLE_NAME=cc.TABLE_NAME and uc.CONSTRAINT_NAME=cc.CONSTRAINT_NAME ")
				.append(" WHERE lower(uc.CONSTRAINT_SCHEMA)='").append(schema.toLowerCase()).append("' AND ")
				.append(" lower(uc.TABLE_NAME) = '").append(tableName.toLowerCase()).append("'");
		boolean needAnd=true;
		boolean needOr=false;
		final int type = typeEnum.getType();
		//PRIMARY KEY   UNIQUE   FOREIGN KEY
		if(type!=0) {
			if(needAnd) {
				sbSql.append(" AND ");
			}
			needAnd=true;
			
			needOr=false;
			sbSql.append("(");
			if((type&1)==1) {
				if(needOr) {
					sbSql.append(" OR ");
				}
				needOr=true;
				sbSql.append(" uc.CONSTRAINT_TYPE='PRIMARY KEY' ");
			}
			if((type&2)==2) {
				if(needOr) {
					sbSql.append(" OR ");
				}
				needOr=true;
				sbSql.append(" uc.CONSTRAINT_TYPE='UNIQUE' ");
			}
			if((type&4)==4) {
				if(needOr) {
					sbSql.append(" OR ");
				}
				needOr=true;
				sbSql.append(" uc.CONSTRAINT_TYPE='FOREIGN KEY' ");
			}

			sbSql.append(")");
		}
		return sbSql.toString();
	}
	
	@Override
	public List<String> sqlOfCreateTable(String tableName, String comment, List<ColumnDefine> colDef, String schema)
	{
		List<String> lsSql=new ArrayList<String>();
		StringBuilder sql=new StringBuilder();
		List<String> pkCol=new ArrayList<String>(colDef.size());
		
		sql.append("CREATE TABLE ").append(getFullTable(schema,tableName)).append("(\n");
		for(int inx=0;inx<colDef.size();++inx) {
			ColumnDefine def=colDef.get(inx);
			if(inx>0) sql.append(",\n");
			sql.append(def.getName()).append(" ").append(getColumnTypeString(def));
			if(!def.isNullable()) {
				sql.append(" NOT NULL");
			}
			if(!StringUtils.isEmpty(def.getComment())) {
				sql.append(" COMMENT '").append(SqlUtil.toSql(def.getComment())).append("'");
			}
			
			if(def.isPrimaryKey()) {
				pkCol.add(def.getName());
			}
		}
		if(pkCol.size()>0) {
			sql.append(",\n");
			sql.append("PRIMARY KEY (");
			sql.append(String.join(",", pkCol));
			sql.append(")");
		}
		sql.append("\n)");
		if(!StringUtils.isEmpty(comment)) {
			sql.append(" COMMENT='").append(SqlUtil.toSql(comment)).append("'");
		}
		
		lsSql.add(sql.toString());
		return lsSql;
	}
	
	@Override
	public String sqlOfModifyTableComment(String comment,String tableName,String schema)
	{
		return "ALTER TABLE "+getFullTable(schema,tableName)
				+" COMMENT='"+SqlUtil.toSql(comment)+"'";
	}

	@Override
	public List<String> sqlOfAddColumn(ColumnDefine colDef,String tableName, String schema)
	{
		List<String> lsSql=new ArrayList<String>();
		String sql;
		sql="ALTER TABLE "+getFullTable(schema,tableName)
				+" ADD COLUMN "+colDef.getName()+" "+getColumnTypeString(colDef)+" "+(colDef.isNullable()?" NULL ":" NOT NULL ")
				+(StringUtils.isEmpty(colDef.getComment())?" ":(" COMMENT '"+SqlUtil.toSql(colDef.getComment())+"'"));
		lsSql.add(sql);
		
		return lsSql;
	}

	@Override
	public List<String> sqlOfModifyColumn(ModifyColumnDefine colDef,String tableName, String schema)
	{
		List<String> lsSql=new ArrayList<String>();
		StringBuilder sbSql=new StringBuilder();
		sbSql.append("ALTER TABLE ");
		sbSql.append(getFullTable(schema,tableName));
		if(colDef.isModifyName()) {
			sbSql.append(" CHANGE COLUMN ").append(colDef.getOldName());
		}else {
			sbSql.append(" MODIFY COLUMN ");
		}
		sbSql.append(" ").append(colDef.getName()).append(" ").append(getColumnTypeString(colDef));
		sbSql.append(" ").append(colDef.isNullable()?"NULL":"NOT NULL");
		if(!StringUtils.isEmpty(colDef.getComment())) {
			sbSql.append(" COMMENT '").append(SqlUtil.toSql(colDef.getComment())).append("'");
		}
		
		lsSql.add(sbSql.toString());
		return lsSql;
	}
	
	//标准写法
//	@Override
//	public String sqlOfDropColumn(String colName, String tableName, String schema)
//	{
//		return String.format("alter table %s drop column %s", getFullTable(schema,tableName),colName);
//	}

	@Override
	public String sqlOfDropPrimaryKey(String keyName, String tableName, String schema)
	{
		return "ALTER TABLE "+getFullTable(schema,tableName)+" drop PRIMARY KEY";
	}
	
	@Override
	public String sqlOfAddUniqueKey(String keyName, String colName, String tableName, String schema)
	{
		return "ALTER TABLE "+getFullTable(schema,tableName)+" add UNIQUE INDEX "+keyName+"("+colName+")";
	}

	@Override
	public String sqlOfDropUniqueKey(String keyName, String tableName, String schema)
	{
		return "ALTER TABLE "+getFullTable(schema,tableName)+" drop INDEX "+keyName;
	}

	@Override
	public String sqlOfDropForeignKey(String keyName, String tableName, String schema)
	{
		return "ALTER TABLE "+getFullTable(schema,tableName)+" drop FOREIGN KEY "+keyName;
	}
	
	@Override
	public String sqlOfEnableForeignKey(String keyName, Boolean enabled, String tableName, String schema)
	{
		return "set foreign_key_checks="+(enabled?"1":"0");
	}

	/**
	 * 重载父类的实现，针对特殊类型特殊处理
	 */
	@Override
	protected String getColumnTypeString(ColumnDefine colDef)
	{
		String typeStr=super.getColumnTypeString(colDef);
//		int len=colDef.getLength();
		if(colDef.getType()==ColumnTypeEnum.Json)
		{
			typeStr="json";
		}
//		else if(colDef.getType()==ColumnTypeEnum.Geometry) {
//			//定义	 point（点）,line（两点直线段）,polyline（折线）,polygon（多边形），mpolygon（空心多边形，第一部分为外边界，剩下每一块描述的是内部空心边界）,rectangle（矩形）,circle（圆）
//			//暂时定义为二进制串
//			if(len==0) len=2000;
//			typeStr=binaryType(len);
//		}
		return typeStr;
	}


}
